﻿CREATE PROCEDURE [dbo].[proc_Statistics_Financial_Tongji_G]
	(
		@CompanyId VARCHAR(500),
		@StartDate nchar(10),
		@EndDate nchar(10)
	)
AS
DECLARE @strSql VARCHAR(max),@strSql1 VARCHAR(max),@strSql2 VARCHAR(max),@strSql3 VARCHAR(max),@strSql4 VARCHAR(max)
	SET @strSql='
	
	Declare @sRq Datetime,@eRq Datetime,@eRq_s Datetime
	Set @sRq = Convert(Datetime, '''+@StartDate + ' 00:00:00'')
	Set @eRq = Convert(Datetime, '''+@EndDate + ' 23:59:59'')
	Set @eRq_s = Convert(Datetime, '''+@EndDate + ' 00:00:00'')
	
	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY (Select M.CompanyNumber From ManagementArea M Where M.Id=Mid) ASC,dwbh)AS Row
	,c.Id As CompanyId
	,c.CompanyName,dwbh,Mid
	,ISNULL((Select Sum(OAA) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As OAA--直接成本
	,ISNULL((Select Sum(OAB) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As OAB--间接成本
	,ISNULL((Select Sum(OBA) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As OBA--市场费用
	,ISNULL((Select Sum(OBB) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As OBB--日常生活
	,ISNULL((Select Sum(OBC) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As OBC--行政办公
	,ISNULL((Select Sum(OBD) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As OBD--财务费用
	,ISNULL((Select Sum(OBE) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As OBE--人力费用
	,ISNULL((Select Sum(OBF) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As OBF--部门基金
	,ISNULL((Select Sum(OBG) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As OBG--总办费用
	,ISNULL((Select Sum(OBH) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As OBH--其他费用
	,ISNULL((Select Sum(OCA) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As OCA--投资
	,ISNULL((Select Sum(OCB) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As OCB--借款
	,ISNULL((Select Sum(OCC) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As OCC--往来借款

	 '
	  SET @strSql3='	                                                                                                                                                                -- 
	,ISNULL((Select Sum(ODA) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As ODA--有形资产
	,ISNULL((Select Sum(ODB) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As ODB--无形资产
	,ISNULL((Select Sum(IAA) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As IAA--新增毛利
	,ISNULL((Select Sum(IAB) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As IAB--续费毛利
	,ISNULL((Select Sum(IAC) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As IAC--新增应收
	,ISNULL((Select Sum(IAD) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As IAD--新增应付
	,ISNULL((Select Sum(IAE) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As IAE--成单金额
	,ISNULL((Select Sum(IAF) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As IAF--尾款收入
	,ISNULL((Select Sum(IAG) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As IAG--非营利性收入
	  '

	SET @strSql1='                                                                                                                                                                  
	,ISNULL((Select IBA From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate<=@eRq),0) As IBA--实有现金
	,ISNULL((Select IBB From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate<=@eRq),0) As IBB--实有银行
	,ISNULL((Select IBC From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate<=@eRq),0) As IBC--账户余额
	,ISNULL((Select Top 1 IBD From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate<=@eRq),0) As IBD--应有现金
	,ISNULL((Select Sum(IBDA) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As IBDA--现金收入
	,ISNULL((Select Sum(IBDB) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As IBDB--现金支出
	,ISNULL((Select Top 1 IBE From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate<=@eRq),0) As IBE--应有银行
	,ISNULL((Select Sum(IBEA) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As IBEA--银行收入
	,ISNULL((Select Sum(IBEB) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As IBEB--银行支出
	,ISNULL((Select Top 1 IBF From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate<=@eRq),0) As IBF--借款合计
	,ISNULL((Select Top 1 IBG From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate<=@eRq),0) As IBG--资金合计
	,ISNULL((Select Sum(IBH) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As IBH--应有现金平整
	,ISNULL((Select Sum(IBHA) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As IBHA--现金平整收入
	,ISNULL((Select Sum(IBHB) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As IBHB--现金平整支出

	'
	SET @strSql2='
		,ISNULL((Select Sum(IBI) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As IBI--应有银行平整
	,ISNULL((Select Sum(IBIA) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As IBIA--银行平整收入
	,ISNULL((Select Sum(IBIB) From Statistics_Financial Where CompanyId=c.Id and CreateDate >= @sRq and CreateDate<=@eRq),0) As IBIB--银行平整支出
	,ISNULL((Select (IBA+IBB)-(IBD+IBE) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate<=@eRq),0) As IBJ--差额
	,ISNULL((Select IBK From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate<=@eRq),0) As IBK--应收合计
	,ISNULL((Select IBL From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate<=@eRq),0) As IBL--应付合计
	From Company c Where ('''+@CompanyId+'''=''0'' Or Id in ('+@CompanyId+'))
	)	
	Select * From list,(SELECT SUM(IAA) AS IAA_a,SUM(IAB) AS IAB_a,SUM(IAC) AS IAC_a,SUM(IAD) AS IAD_a,SUM(IAE) AS IAE_a,
	SUM(IAF) AS IAF_a,SUM(IAG) AS IAG_a 
	
	
	 '
	 
	 SET @strSql4=',SUM(OAA) AS OAA_a,SUM(OAB) AS OAB_a,SUM(OBA) AS OBA_a,SUM(OBB) AS OBB_a
	,SUM(OBC) AS OBC_a,SUM(OBD) AS OBD_a,SUM(OBE) AS OBE_a,SUM(OBF) AS OBF_a
	,SUM(OBG) AS OBG_a,SUM(OBH) AS OBH_a,SUM(OCA) AS OCA_a,SUM(OCB) AS OCB_a,SUM(OCC) AS OCC_a
	,SUM(ODA) AS ODA_a,SUM(ODB) AS ODB_a,
	
	SUM(IBA) AS IBA_a,SUM(IBB) AS IBB_a,SUM(IBD) AS IBD_a,SUM(IBE) AS IBE_a
	,SUM(IBC) AS IBC_a,SUM(IBF) AS IBF_a,SUM(IBG) AS IBG_a,SUM(IBH) AS IBH_a
	,SUM(IBI) AS IBI_a,SUM(IBJ) AS IBJ_a,SUM(IBK) AS IBK_a,SUM(IBL) AS IBL_a
	 FROM list) AS n ORDER BY (Select M.CompanyNumber From ManagementArea M Where M.Id=Mid) ASC,dwbh
	 
	'
	 
	--PRINT (@strSql+@strSql3+@strSql1+@strSql2+@strSql4)
	--PRINT (@strSql1)
EXEC(@strSql+@strSql3+@strSql1+@strSql2+@strSql4)

	RETURN
